package com.dy.yunying.biz.dao.cps;

import com.dy.yunying.api.cps.dto.DataReportDto;
import com.dy.yunying.api.cps.vo.DataReportVO;
import com.dy.yunying.biz.config.YunYingProperties;
import com.dy.yunying.biz.utils.DateUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Objects;
import java.util.stream.Collectors;

/**
 * @author leisw
 * @date 2022/9/15 11:22
 */
@Slf4j
@Component(value = "dataReportDao")
public class DataReportDao {

	@Resource(name = "dorisTemplate")
	private JdbcTemplate dorisTemplate;

	@Resource
	private YunYingProperties yunYingProperties;


	public Long count(DataReportDto req) {
		final StringBuilder countSql = new StringBuilder();
		countSql.append("SELECT\n");
		countSql.append("    COUNT(1)\n");
		countSql.append("FROM\n");
		countSql.append("    (\n");
		countSql.append(getListSql(req));
		countSql.append("    ) a\n");
		String sql=countSql.toString();
		log.info("countSQL:{}",sql);
		return dorisTemplate.queryForObject(sql, Long.class);
	}


	public List<DataReportVO> list(DataReportDto req) {

		StringBuilder sql=	getListSql(req);
		Long current = req.getCurrent();
		Long size = req.getSize();
		if (Objects.nonNull(current) && Objects.nonNull(size)) {
			Long offset = (current - 1) * size;
			sql.append("LIMIT\n");
			sql.append("    ").append(offset).append(", ").append(size).append('\n');
		}
		log.info("cps报表数据分列表查询SQL: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();
		List<DataReportVO> dailyDataVOList = dorisTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(DataReportVO.class));
		log.info("cps报表数据分列表页查询耗时: {}ms", System.currentTimeMillis() - start);
		return dailyDataVOList;
	}


	/**
	 * 获取list 查询sql
	 * @param req
	 * @return
	 */
	private  StringBuilder getListSql(DataReportDto req) {
		StringBuilder sql = new StringBuilder();
		String activityRechargeTable = yunYingProperties.getDailyDataDeviceActiveTable();
		String deviceRegTable = yunYingProperties.getDailyDataDeviceRegTableData();
		String accountRegTable = yunYingProperties.getDailyDataAccountRegTableData();
		Integer dxAppCode = yunYingProperties.getDxAPPCode();
		String cpsChlMain = yunYingProperties.getCpsParentChannelId().toString();

		sql.append("SELECT \n");
		sql.append("period,\n" );
		sql.append(StringUtils.isNotEmpty(req.getQueryColumn()) ? this.getStartColumnSql(req, "") + "  \n" : " \n ");
		sql.append("\tsum(accountNums) accountNums,-- 新增设备数(激活数)\n"+
				"\tsum(accountRegNums) accountRegNums,-- 新增设备注册数\n"+
				"\tround( IF ( sum(accountNums) > 0, divide ( sum(accountRegNums) * 100.00, sum(accountNums) ), 0 ), 2 ) AS accountNumRate,-- 激活率\n"+
				"\tsum(newPayNums) newPayNums,-- 新账号充值人数\n"+
				"\tround( IF ( sum(accountRegNums) > 0, divide ( sum(newPayNums) * 100.00, sum(accountRegNums) ), 0 ), 2 ) AS newpayRate,-- 新增付费率\n"+
				"\tsum(newPayAmounts) newPayAmounts,-- 新账号充值金额\n"+
				"\tsum(newPayFeeAmounts) newPayFeeAmounts,-- 新账号实付充值金额\n"+
				"\tsum(periodTotalPayFeeAmount) periodTotalPayFeeAmount,-- 期内充值实付金额\n"+
				"\tsum(activeaccounts) activeaccounts,-- 活跃用户数\n"+
				"\tsum(activeFeeAccounts) activeFeeAccounts,-- 活跃付费数\n"+
				"\tround( IF ( sum(activeaccounts) > 0, divide ( sum(activeFeeAccounts) * 100.00, sum(activeaccounts) ), 0 ), 2 ) AS accountpayRate,-- 活跃付费率\n"+
				"\tsum(activePayAmounts) activePayAmounts,-- 活跃充值金额\n"+
				"\tsum(activePayFeeAmounts) activePayFeeAmounts,-- 活跃充值实付金额\n"+
				"\tsum(totalpaynums) totalpaynums,-- 累计付费数\n"+
				"\tsum(totalpayamounts) totalpayamounts,-- 累计充值金额\n"+
				"\tsum(totalpayfeeamounts) totalpayfeeamounts-- 累计充值实付金额\n"+
				"FROM\n" +
				"(")
				.append(getActivePaySql(req, activityRechargeTable, deviceRegTable,dxAppCode)).append("\n")
				.append("UNION ALL ").append("\n")
				.append(getNewDeviceSql(req,deviceRegTable,dxAppCode)).append("\n")
				.append("UNION ALL ").append("\n")
				.append(getActiveDeviceSql(req,activityRechargeTable,dxAppCode)).append("\n")
				.append("UNION ALL ").append("\n")
				.append(getNewDeviceRegSql(req, deviceRegTable, accountRegTable,dxAppCode)).append("\n");
		if (StringUtils.isNotEmpty(req.getPayStartTime()) || StringUtils.isNotEmpty(req.getPayEndTime())) {
			sql.append("UNION ALL ").append("\n")
					.append(getPeriodPaySql(req,deviceRegTable,activityRechargeTable,dxAppCode)).append("\n");
		}

		sql.append("UNION ALL ").append("\n")
				.append(getAccumulatedDataSql(req,deviceRegTable,activityRechargeTable,dxAppCode)).append("\n");

		sql.append(") a").append("\n")
				.append("group by ").append("period").append(StringUtils.isNotBlank(req.getQueryColumn()) ?  getEndColumnSql(req, "") + " \n" : " \n");

		if (StringUtils.isNotBlank(req.getKpiValue()) && StringUtils.isNotBlank(req.getSort())) {
			sql.append("ORDER BY\n");
			sql.append("    ").append(req.getKpiValue()).append(" ").append(req.getSort()).append('\n');
		} else {
			sql.append("ORDER BY\n");
			sql.append("    period DESC\n");
		}
		return  sql;

	}

	private StringBuilder getPeriodPaySql(DataReportDto req, String deviceRegTable, String activityRechargeTable, Integer dxAppCode) {
		StringBuilder periodPaySql = new StringBuilder();
		periodPaySql.append("      SELECT \n")
				.append("       period,\n" )
				.append("       ").append(StringUtils.isNotEmpty(req.getQueryColumn()) ? this.getStartColumnSql(req, "") + "  \n" : " \n ")
				.append("       0 AS accountNums,  -- 新增设备数\n")
				.append("       0 AS accountRegNums,  -- 新增设备注册数\n")
				.append("       0 AS newPayNums,-- 新增付费数\n")
				.append("       0 AS newPayAmounts,-- 新增充值金额\n")
				.append("       0 AS newPayFeeAmounts,-- 新增充值实付金额\n")
				.append("       sum(periodtotalpayfeeamount) AS periodTotalPayFeeAmount,-- 期内充值实付金额\n")
				.append("       0 AS activeaccounts,  -- 活跃用户数\n")
				.append("       0 AS activeFeeAccounts, -- 活跃付费数\n")
				.append("       0 AS activePayAmounts, -- 活跃充值金额\n")
				.append("       0 AS activePayFeeAmounts,-- 活跃充值实付金额\n")
				.append("       0 AS totalpaynums,-- 累计付费数\n")
				.append("       0 AS totalpayamounts,-- 累计充值金额\n")
				.append("       0 AS totalpayfeeamounts-- 累计充值实付金额\n")
				.append("   FROM (\n")
				.append("       SELECT")
				.append("           b.period AS period,")
				.append("           b.app_code AS app_code,\n")
				.append("           b.chl_sub  AS chl_sub,\n")
				.append("           b.chl_app  AS chl_app,\n")
				.append("           IF(a.pay_fee_amount > 0, floor(if(d.totalpayfeeamount > ").append(yunYingProperties.getBuckleRateCalculationThreshold()).append(", a.pay_fee_amount*(1-c.buckle_rate/100), a.pay_fee_amount)), 0 ) periodtotalpayfeeamount -- 期内充值实付金额\n")
				.append("       FROM  (\n")
				.append("          SELECT\n")
				.append(this.getPeriodSql(req)).append(" AS period, ")
				.append("            day as active_day,app_code,app_main,uuid,chl_main,chl_sub,chl_app,pay_amount,pay_fee_amount ,give_money\n")
				.append("          FROM \n")
				.append("             ").append(activityRechargeTable)
				.append("             where 1=1 and dx_app_code = ").append(dxAppCode).append(" and is_pay = 1 \n");
		if (StringUtils.isNotEmpty(req.getPayStartTime())){
			periodPaySql.append(" and from_unixtime(pay_time/1000, 'yyyyMMdd') >= ").append(req.getPayStartTime()).append(" ");
		}
		if (StringUtils.isNotEmpty(req.getPayEndTime())){
			periodPaySql.append(" and from_unixtime(pay_time/1000, 'yyyyMMdd') <= ").append(req.getPayEndTime()).append(" ");
		}
		periodPaySql.append(getWhereConditionGameId(req)).append("\n")
				.append("          )  a  \n")
				.append("inner JOIN ( \n")
				.append("SELECT\n")
				.append(this.getPeriodSql(req)).append(" AS period,")
				.append("day as reg_day,app_code,app_main,uuid,chl_sub,chl_app,chl_main").append("\n")
				.append("FROM  \n")
				.append(deviceRegTable).append("\n")
				.append("where 1=1 ").append("\n")
				.append(" and  dx_app_code = ").append(dxAppCode) //去掉渠道主渠道限制
				.append(" and day >= ").append(req.getStartTime())
				.append(" and day <= ").append(req.getEndTime()).append("\n")
				.append(getWhereCondition(req)).append("\n")
				.append(") b\n")
				.append("ON a.app_code = b.app_code AND a.uuid =  b.uuid  and a.app_main = b.app_main \n")
				.append("           left JOIN (  \n")
				.append("               SELECT\n")
				.append("                 day,\n")
				.append("                 chl_app,\n")
				.append("                 sum(pay_fee_amount) as totalpayfeeamount -- 累计实付金额\n")
				.append("               FROM\n")
				.append("                  ").append(activityRechargeTable)
				.append("               WHERE dx_app_code = ").append(dxAppCode).append(" and is_pay = 1 ")
				.append(getWhereConditionGameId(req)).append(" group by chl_app, `day`\n")
				.append("            )  d  ON a.active_day = d.`day` and a.chl_app =d.chl_app\n");
		periodPaySql.append("   inner JOIN (  SELECT a.chl_app, a.`day`, max(buckle_rate) buckle_rate FROM (\n");
		periodPaySql.append("      SELECT \n");
		//activePaySql.append("             a.chl_sub,  \n");
		periodPaySql.append("             a.chl_app,   \n");
		periodPaySql.append("             a.`day`,  \n");
		periodPaySql.append("             if(a.`day` >= DATE_FORMAT(b.start_time ,'yyyyMMdd') AND a.`day` <=  DATE_FORMAT(b.end_time ,'yyyyMMdd'), b.buckle_rate, 0) buckle_rate,  \n");
		periodPaySql.append("             b.user_id  \n");
		periodPaySql.append("      FROM \n")
				.append("               (   \n")
				.append("          		SELECT  \n")
				.append("            	       a.app_chl AS chl_app,\n" )
				.append("           	   	   DATE_FORMAT(dd.cday ,'yyyyMMdd') as day  \n")
				.append("           	  FROM (\n")
				.append("                   SELECT DISTINCT vcup.app_chl FROM \n")
				.append("             		  ").append(yunYingProperties.getCpsUserPackTable()).append("  vcup WHERE vcup.op_tag = 1 ) a , ").append(yunYingProperties.getDimDayInfo()).append("  dd \n")
				.append("                      where 1=1 ").append(" and DATE_FORMAT(dd.cday ,'yyyyMMdd') >= ").append(req.getPayStartTime()).append(" and DATE_FORMAT(dd.cday ,'yyyyMMdd') <= ").append(req.getPayEndTime()).append("\n")
				.append("                 ) a \n");
		periodPaySql.append("           left JOIN ( \n");
		periodPaySql.append("                 SELECT \n");
		periodPaySql.append("                    cbr.buckle_rate, \n");
		periodPaySql.append("                    cbr.start_time, \n");
		periodPaySql.append("                    cbr.end_time, \n");
		periodPaySql.append("                    cup.user_id, \n");
		periodPaySql.append("                    cup.parent_chl, \n");
		periodPaySql.append("                    cup.chl, \n");
		periodPaySql.append("                    cup.app_chl, \n");
		periodPaySql.append("                    cup.op_tag \n");
		periodPaySql.append("                 FROM \n");
		periodPaySql.append("                    ").append(yunYingProperties.getCpsUserPackTable()).append("  cup \n");
		periodPaySql.append("                 LEFT JOIN ").append(yunYingProperties.getCpsBuckleRateTable()).append("  cbr  ON cbr.user_id = cup.user_id\n");
		periodPaySql.append("                   ) b ON  a.chl_app = b.app_chl ");
		periodPaySql.append("           WHERE\n");
		periodPaySql.append("             1 = 1 and b.op_tag = 1 \n");
		if (req.getIsSys() != 1) {
			periodPaySql.append("   AND b.user_id  IN (").append(req.getUserIds()).append(")\n"); // -- 渠道权限
		}
		periodPaySql.append("       )  a \n");
		periodPaySql.append("       GROUP BY  a.chl_app, a.`day` ) c ON c.chl_app = b.chl_app  and  a.active_day = c.day\n");





//				.append("            inner JOIN ( \n")
//				.append("            	SELECT\n")
//				.append("            		 a.chl_main, a.chl_sub,a.chl_app, a.`day`, max(buckle_rate) buckle_rate \n")
//				.append("            	FROM ( \n")
//				.append("            		SELECT a.chl_main, \n")
//				.append("            	           a.chl_sub,  \n")
//				.append("            	 		   a.chl_app, \n")
//				.append("            	 		   a.`day`,  \n")
//				.append("            	 		   if(a.`day` >= DATE_FORMAT(b.start_time ,'yyyyMMdd') AND a.`day` <=  DATE_FORMAT(b.end_time ,'yyyyMMdd'), b.buckle_rate, 0) buckle_rate, \n")
//				.append("            	 		   b.user_id   \n")
//				.append("            	 	FROM  \n")
//				.append("            	 	    (   \n")
//				.append("            				SELECT chl_main, \n")
//				.append("            	           		chl_sub,  \n")
//				.append("            	 		   		chl_app, \n")
//				.append("            	 		   		`day`  \n")
//				.append("           	             FROM \n")
//				.append("            	 	  		").append(activityRechargeTable)
//				.append("                          where 1=1 ").append("\n")
//				.append("                             and  dx_app_code = ").append(dxAppCode)
//				.append("                             ").append(getWhereConditionGameId(req)).append("\n")
//				.append("                      ) a \n")
//				.append("            	 	inner JOIN ( \n")
//				.append("            	 		SELECT  \n")
//				.append("            	 	cbr.buckle_rate, \n")
//				.append("            	 	cbr.start_time, \n")
//				.append("            	 	cbr.end_time, \n")
//				.append("            	 	cup.user_id, \n")
//				.append("            	 	cup.parent_chl, \n")
//				.append("            	 	cup.chl, \n")
//				.append("            	 	cup.app_chl, \n")
//				.append("            	 	cup.op_tag \n")
//				.append("                 FROM ")
//				.append("                    ").append(yunYingProperties.getCpsUserPackTable()).append("  cup \n")
//				.append("                  LEFT JOIN ").append(yunYingProperties.getCpsBuckleRateTable()).append(" cbr ON cbr.user_id = cup.user_id \n")
//				.append("                   ) b ON a.chl_main = b.parent_chl AND a.chl_sub = b.chl and a.chl_app = b.app_chl ")
//				.append("           WHERE\n")
//				.append("             1 = 1 and b.op_tag = 1\n");
//		if (req.getIsSys() != 1) {
//			periodPaySql.append("   AND b.user_id  IN (").append(req.getUserIds()).append(")\n"); // -- 渠道权限
//		}
//		periodPaySql.append("       )  a \n");
//		periodPaySql.append("       GROUP BY  a.chl_app, a.`day`, a.chl_main, a.chl_sub  ) c ON c.chl_main = a.chl_main AND  c.chl_sub = a.chl_sub and c.chl_app = a.chl_app  and a.active_day = c.day \n");
		periodPaySql.append("  )  a \n");
		periodPaySql.append("group by ").append("period").append(StringUtils.isNotBlank(req.getQueryColumn()) ? getEndColumnSql(req, "") + " \n" : " \n");



		return periodPaySql;
	}

	private StringBuilder getAccumulatedDataSql(DataReportDto req,String deviceRegTable, String activityRechargeTable, Integer dxAppCode) {
		StringBuilder accumulatedDataSql = new StringBuilder();
		accumulatedDataSql.append("      SELECT \n")
				.append("       period,\n" )
				.append("       ").append(StringUtils.isNotEmpty(req.getQueryColumn()) ? this.getStartColumnSql(req, "") + "  \n" : " \n ")
				.append("       0 AS accountNums,  -- 新增设备数\n")
				.append("       0 AS accountRegNums,  -- 新增设备注册数\n")
				.append("       0 AS newPayNums,-- 新增付费数\n")
				.append("       0 AS newPayAmounts,-- 新增充值金额\n")
				.append("       0 AS newPayFeeAmounts,-- 新增充值实付金额\n")
				.append("       0 AS periodTotalPayFeeAmount,-- 期内充值实付金额\n")
				.append("       0 AS activeaccounts,  -- 活跃用户数\n")
				.append("       0 AS activeFeeAccounts, -- 活跃付费数\n")
				.append("       0 AS activePayAmounts, -- 活跃充值金额\n")
				.append("       0 AS activePayFeeAmounts,-- 活跃充值实付金额\n")
				.append("       count(DISTINCT totalpaynum) AS totalpaynums,-- 累计付费数\n")
				.append("       sum(totalpayamount) AS totalpayamounts,-- 累计充值金额\n")
				.append("       sum(totalpayfeeamount) AS totalpayfeeamounts -- 累计充值实付金额\n")
				.append("   FROM (\n")
				.append("       SELECT")
				.append("           b.period AS period,")
				.append("           b.app_code AS app_code,\n")
				.append("           b.chl_sub  AS chl_sub,\n")
				.append("           b.chl_app  AS chl_app,\n")
				.append("           IF(a.pay_amount > 0, a.uuid, NULL ) totalpaynum,-- 累积付费人数\n")
				.append("           IF(a.pay_amount > 0, a.pay_amount, 0 ) totalpayamount, -- 累积付费金额,\n")
				.append("           IF(a.pay_fee_amount > 0, floor(if(d.totalpayfeeamount > ").append(yunYingProperties.getBuckleRateCalculationThreshold()).append(", a.pay_fee_amount*(1-c.buckle_rate/100), a.pay_fee_amount)), 0 ) totalpayfeeamount -- 累积实付金额\n")
				.append("       FROM  (\n")
				.append("          SELECT\n")
				.append(this.getPeriodSql(req)).append(" AS period, ")
				.append("            day as active_day,app_code,app_main,uuid,chl_main,chl_sub,chl_app,pay_amount,pay_fee_amount ,give_money\n")
				.append("          FROM \n")
				.append("             ").append(activityRechargeTable)
				.append("             where 1=1 and dx_app_code = ").append(dxAppCode).append(" and is_pay = 1 \n")
				.append(getWhereConditionGameId(req)).append("\n")
				.append("          )  a  \n")
				.append("inner JOIN ( \n")
				.append("SELECT\n")
				.append(this.getPeriodSql(req)).append(" AS period,")
				.append("day as reg_day,app_code,app_main,uuid,chl_sub,chl_app,chl_main").append("\n")
				.append("FROM  \n")
				.append(deviceRegTable).append("\n")
				.append("where 1=1 ").append("\n")
				.append(" and  dx_app_code = ").append(dxAppCode) //去掉渠道主渠道限制
				.append(" and day >= ").append(req.getStartTime())
				.append(" and day <= ").append(req.getEndTime()).append("\n")
				.append(getWhereCondition(req)).append("\n")
				.append(") b\n")
				.append("ON a.app_code = b.app_code AND a.uuid =  b.uuid  and a.app_main = b.app_main\n")
				.append("           left JOIN (  \n")
				.append("               SELECT\n")
				.append("                 day,\n")
				.append("                 chl_app,\n")
				.append("                 sum(pay_fee_amount) as totalpayfeeamount -- 累计实付金额\n")
				.append("               FROM\n")
				.append("                  ").append(activityRechargeTable)
				.append("               WHERE dx_app_code = ").append(dxAppCode).append(" and is_pay = 1 ").append(getWhereCondition(req)).append(" group by chl_app, `day`\n")
				.append("            )  d  ON a.active_day = d.`day` and a.chl_app =d.chl_app\n")

		        .append("   inner JOIN (  SELECT a.chl_app, a.`day`, max(buckle_rate) buckle_rate FROM (\n")
		        .append("      SELECT \n")
		        //activePaySql.append("             a.chl_sub,  \n");
		        .append("             a.chl_app,   \n")
		        .append("             a.`day`,  \n")
		        .append("             if(a.`day` >= DATE_FORMAT(b.start_time ,'yyyyMMdd') AND a.`day` <=  DATE_FORMAT(b.end_time ,'yyyyMMdd'), b.buckle_rate, 0) buckle_rate,  \n")
		        .append("             b.user_id  \n")
		        .append("      FROM \n")
				.append("               (   \n")
				.append("          		SELECT  \n")
				.append("            	       a.app_chl AS chl_app,\n" )
				.append("           	   	   DATE_FORMAT(dd.cday ,'yyyyMMdd') as day  \n")
				.append("           	  FROM (\n")
				.append("                   SELECT DISTINCT vcup.app_chl FROM \n")
				.append("             		  ").append(yunYingProperties.getCpsUserPackTable()).append("  vcup WHERE vcup.op_tag = 1 ) a , ").append(yunYingProperties.getDimDayInfo()).append("  dd \n")
				.append("                      where 1=1 ").append(" and DATE_FORMAT(dd.cday ,'yyyyMMdd') >= ").append(req.getStartTime()).append(" and DATE_FORMAT(dd.cday ,'yyyyMMdd') <= ").append(DateUtils.dateToString(new Date(),DateUtils.YYYYMMDD)).append("\n")
				.append("                 ) a \n")
		        .append("           left JOIN ( \n")
		        .append("                 SELECT \n")
		        .append("                    cbr.buckle_rate, \n")
		        .append("                    cbr.start_time, \n")
		        .append("                    cbr.end_time, \n")
		        .append("                    cup.user_id, \n")
		        .append("                    cup.parent_chl, \n")
		        .append("                    cup.chl, \n")
		        .append("                    cup.app_chl, \n")
		        .append("                    cup.op_tag \n")
		        .append("                 FROM \n")
		        .append("                    ").append(yunYingProperties.getCpsUserPackTable()).append("  cup \n")
		        .append("                 LEFT JOIN ").append(yunYingProperties.getCpsBuckleRateTable()).append("  cbr  ON cbr.user_id = cup.user_id\n")
		        .append("                   ) b ON  a.chl_app = b.app_chl \n")
		        .append("           WHERE\n")
		        .append("             1 = 1 and b.op_tag = 1 \n");
		        if (req.getIsSys() != 1) {
					accumulatedDataSql.append("   AND b.user_id  IN (").append(req.getUserIds()).append(")\n"); // -- 渠道权限
		        }
		accumulatedDataSql.append("       )  a \n")
		        .append("       GROUP BY  a.chl_app, a.`day` ) c ON c.chl_app = b.chl_app  and  a.active_day = c.day\n");
//				.append("            inner JOIN ( \n")
//				.append("            	SELECT\n")
//				.append("            		 a.chl_main, a.chl_sub,a.chl_app, a.`day`, max(buckle_rate) buckle_rate \n")
//				.append("            	FROM ( \n")
//				.append("            		SELECT a.chl_main, \n")
//				.append("            	           a.chl_sub,  \n")
//				.append("            	 		   a.chl_app, \n")
//				.append("            	 		   a.`day`,  \n")
//				.append("            	 		   if(a.`day` >= DATE_FORMAT(b.start_time ,'yyyyMMdd') AND a.`day` <=  DATE_FORMAT(b.end_time ,'yyyyMMdd'), b.buckle_rate, 0) buckle_rate, \n")
//				.append("            	 		   b.user_id   \n")
//				.append("            	 	FROM  \n")
//				.append("            	 	    (   \n")
//				.append("            				SELECT chl_main, \n")
//				.append("            	           		chl_sub,  \n")
//				.append("            	 		   		chl_app, \n")
//				.append("            	 		   		`day`  \n")
//				.append("           	             FROM \n")
//				.append("            	 	  		").append(activityRechargeTable)
//				.append("                          where 1=1 ").append("\n")
//				.append("                             and  dx_app_code = ").append(dxAppCode) //去掉渠道主渠道限制
//				.append("                             ").append(getWhereConditionGameId(req)).append("\n")
//				.append("                      ) a \n")
//				.append("            	 	inner JOIN ( \n")
//				.append("            	 		SELECT  \n")
//				.append("            	 	cbr.buckle_rate, \n")
//				.append("            	 	cbr.start_time, \n")
//				.append("            	 	cbr.end_time, \n")
//				.append("            	 	cup.user_id, \n")
//				.append("            	 	cup.parent_chl, \n")
//				.append("            	 	cup.chl, \n")
//				.append("            	 	cup.app_chl, \n")
//				.append("            	 	cup.op_tag \n")
//				.append("                 FROM ")
//				.append("                    ").append(yunYingProperties.getCpsUserPackTable()).append("  cup \n")
//				.append("                  LEFT JOIN ").append(yunYingProperties.getCpsBuckleRateTable()).append(" cbr ON cbr.user_id = cup.user_id \n")
//		    	.append("                   ) b ON a.chl_main = b.parent_chl AND a.chl_sub = b.chl and a.chl_app = b.app_chl ")
//				.append("           WHERE\n")
//				.append("             1 = 1 and b.op_tag = 1\n");
//		if (req.getIsSys() != 1) {
//			accumulatedDataSql.append("   AND b.user_id  IN (").append(req.getUserIds()).append(")\n"); // -- 渠道权限
//		}
//		accumulatedDataSql.append("       )  a \n");
//		accumulatedDataSql.append("       GROUP BY  a.chl_app, a.`day`, a.chl_main, a.chl_sub  ) c ON c.chl_main = b.chl_main AND  c.chl_sub = b.chl_sub and c.chl_app = b.chl_app  and a.active_day = c.day \n");
		accumulatedDataSql.append("  )  a \n");
		accumulatedDataSql.append("group by ").append("period").append(StringUtils.isNotBlank(req.getQueryColumn()) ? getEndColumnSql(req, "") + " \n" : " \n");



		return accumulatedDataSql;
	}

	/**
	 * 活跃,新增,累计
	 * @param req
	 * @param activityRechargeTable
	 * @param deviceRegTable
	 * @return
	 */
	private StringBuilder getActivePaySql(DataReportDto req, String activityRechargeTable, String deviceRegTable ,Integer dxAppCode) {
		StringBuilder activePaySql = new StringBuilder();
		activePaySql.append("SELECT \n")
				.append("period,\n" )
				.append(StringUtils.isNotEmpty(req.getQueryColumn()) ? this.getStartColumnSql(req, "") + "  \n" : " \n ")
				.append("\t0 AS accountNums,  -- 新增设备数\n" +
						"\t0 AS accountRegNums,  -- 新增设备注册数\n" +
						"\tCOUNT( DISTINCT newpaynum ) AS newPayNums,-- 新增付费数\n" +
						"\tsum( newpayamount ) AS newPayAmounts,-- 新增充值金额\n" +
						"\tsum( newpayfeeamount ) AS newPayFeeAmounts,-- 新增充值实付金额\n" +
						"\t0 AS periodTotalPayFeeAmount,-- 期内充值实付金额\n" +
						"\t0 AS activeaccounts,  -- 活跃用户数\n" +
						"\tCOUNT( DISTINCT paynum ) AS activeFeeAccounts, -- 活跃付费数\n" +
						"\tSUM( pay_amount ) AS activePayAmounts, -- 活跃充值金额\n" +
						"\tSUM( pay_fee_amount ) AS activePayFeeAmounts,-- 活跃充值实付金额\n" +
						"\t0 AS totalpaynums,-- 累计付费数\n" +
						"\t0 AS totalpayamounts,-- 累计充值金额\n" +
						"\t0  AS totalpayfeeamounts -- 累计充值实付金额\n");
		activePaySql.append("FROM (\n")
				.append("SELECT\n")
				.append("\ta.period AS period,\n" +
						"\tIF(b.app_code is null ,a.app_code,b.app_code ) AS app_code,\n" +
						"\tIF(b.chl_sub is null ,a.chl_sub,b.chl_sub ) AS chl_sub,\n" +
						"\tIF(b.chl_app is null ,a.chl_app,b.chl_app ) AS chl_app,\n")
				.append("\tIF(a.uuid is not null, a.uuid ,NULL) AS activeaccounts,  -- 活跃账号数\n")
				.append("\tIF(a.pay_amount > 0 , a.pay_amount ,0) AS pay_amount,-- 付费金额\n")
				.append("\tIF(a.pay_fee_amount > 0,floor(if(d.totalpayfeeamount > ").append(yunYingProperties.getBuckleRateCalculationThreshold()).append(", a.pay_fee_amount*(1-c.buckle_rate/100),a.pay_fee_amount)),0) AS pay_fee_amount,-- 实付金额\n")
				.append("\tIF(a.pay_amount > 0  and a.uuid is not null, a.uuid, NULL ) paynum,-- 付费人数\n" +
						"\tIF(a.active_day = b.reg_day  AND a.pay_amount > 0 , a.uuid, NULL ) newpaynum,-- 新增付费人数\n" +
						"\tIF(a.active_day = b.reg_day   AND a.pay_amount > 0, a.pay_amount, 0 ) newpayamount, -- 新付费金额,\n" +
						"\tIF(a.active_day = b.reg_day  AND  a.pay_fee_amount > 0, floor(if(d.totalpayfeeamount > ").append(yunYingProperties.getBuckleRateCalculationThreshold()).append(", a.pay_fee_amount*(1-c.buckle_rate/100), a.pay_fee_amount)), 0 ) newpayfeeamount -- 新实付金额\n" );
//				.append("\tIF(a.pay_amount > 0, a.uuid, NULL ) totalpaynum,-- 累积付费人数 \n")
//				.append("\tIF(a.pay_amount > 0, floor(if(d.totalpayfeeamount > ").append(yunYingProperties.getBuckleRateCalculationThreshold()).append(", a.pay_amount*(1-c.buckle_rate/100), a.pay_amount)), 0 ) totalpayamount, -- 累积付费金额\n" )
//				.append("\tIF(a.pay_fee_amount > 0, floor(if(d.totalpayfeeamount > ").append(yunYingProperties.getBuckleRateCalculationThreshold()).append(", a.pay_fee_amount*(1-c.buckle_rate/100), a.pay_fee_amount)), 0 ) totalpayfeeamount -- 累积实付金额\n" );

		activePaySql.append("FROM  (  \n")
				.append("SELECT\n");
		activePaySql.append(this.getPeriodSql(req)).append(" AS period,");
		activePaySql.append("day as active_day,app_code,app_main,uuid,chl_main,chl_sub,chl_app,pay_amount,pay_fee_amount ,give_money").append("\n");
		activePaySql.append("FROM  \n");
		activePaySql.append(activityRechargeTable).append("\n")
				.append("where 1=1").append("\n")
				.append(" and dx_app_code =  ").append(dxAppCode).append(" and is_pay = 1 ")
				.append(" and day >= ").append(req.getStartTime())
				.append(" and day <= ").append(req.getEndTime()).append("\n");
		activePaySql.append(getWhereConditionGameId(req)).append("\n");
		activePaySql.append(" )  a  \n");

		activePaySql.append(" inner JOIN ( \n")
				.append("SELECT\n");
		activePaySql.append(this.getPeriodSql(req)).append(" AS period,");
		activePaySql.append("day as reg_day,app_code,app_main,uuid,chl_sub,chl_app,chl_main").append("\n");
		activePaySql.append("FROM  \n");
		activePaySql.append(deviceRegTable).append("\n")
				.append("where 1=1 ").append("\n")
				.append(" and  dx_app_code = ").append(dxAppCode) //去掉渠道主渠道限制
				.append(" and day <= ").append(req.getEndTime()).append("\n")
				.append(getWhereCondition(req)).append("\n")
				.append(") b\n");
		activePaySql.append("ON a.app_code = b.app_code AND a.uuid =  b.uuid  and a.app_main = b.app_main\n");
		activePaySql.append(" left JOIN (  \n")
				.append("SELECT \n" +
						"\tday,\n" +
						"\tchl_app,\n" +
						"\tsum(pay_fee_amount) as totalpayfeeamount -- 累计实付金额\n" +
						"FROM \n");
		activePaySql.append(activityRechargeTable).append("\n")
				.append("where 1=1\n" +
						" and dx_app_code = ").append(dxAppCode).append(" and is_pay = 1  ")
				.append(getWhereConditionGameId(req)).append("  group by chl_app, `day` \n");
		activePaySql.append(" )  d\n");
		activePaySql.append(" ON a.active_day = d.`day` and a.chl_app = d.chl_app ").append("\n");

		activePaySql.append("   inner JOIN (  SELECT a.chl_app, a.`day`, max(buckle_rate) buckle_rate FROM (\n");
		activePaySql.append("      SELECT \n");
		//activePaySql.append("             a.chl_sub,  \n");
		activePaySql.append("             a.chl_app,   \n");
		activePaySql.append("             a.`day`,  \n");
		activePaySql.append("             if(a.`day` >= DATE_FORMAT(b.start_time ,'yyyyMMdd') AND a.`day` <=  DATE_FORMAT(b.end_time ,'yyyyMMdd'), b.buckle_rate, 0) buckle_rate,  \n");
		activePaySql.append("             b.user_id  \n");
		activePaySql.append("      FROM \n")
				.append("               (   \n")
				.append("          		SELECT  \n")
				.append("            	       a.app_chl AS chl_app,\n" )
				.append("           	   	   DATE_FORMAT(dd.cday ,'yyyyMMdd') as day  \n")
				.append("           	  FROM (\n")
				.append("                   SELECT DISTINCT vcup.app_chl FROM \n")
				.append("             		  ").append(yunYingProperties.getCpsUserPackTable()).append("  vcup WHERE vcup.op_tag = 1 ) a , ").append(yunYingProperties.getDimDayInfo()).append("  dd \n")
				.append("                      where 1=1 ").append(" and DATE_FORMAT(dd.cday ,'yyyyMMdd') >= ").append(req.getStartTime()).append(" and DATE_FORMAT(dd.cday ,'yyyyMMdd') <= ").append(req.getEndTime()).append("\n")
				.append("                 ) a \n");
		activePaySql.append("           left JOIN ( \n");
		activePaySql.append("                 SELECT \n");
		activePaySql.append("                    cbr.buckle_rate, \n");
		activePaySql.append("                    cbr.start_time, \n");
		activePaySql.append("                    cbr.end_time, \n");
		activePaySql.append("                    cup.user_id, \n");
		activePaySql.append("                    cup.parent_chl, \n");
		activePaySql.append("                    cup.chl, \n");
		activePaySql.append("                    cup.app_chl, \n");
		activePaySql.append("                    cup.op_tag \n");
		activePaySql.append("                 FROM \n");
		activePaySql.append("                    ").append(yunYingProperties.getCpsUserPackTable()).append("  cup \n");
		activePaySql.append("                 LEFT JOIN ").append(yunYingProperties.getCpsBuckleRateTable()).append("  cbr  ON cbr.user_id = cup.user_id\n");
		activePaySql.append("                   ) b ON  a.chl_app = b.app_chl ");
		activePaySql.append("           WHERE\n");
		activePaySql.append("             1 = 1 and b.op_tag = 1 \n");
		if (req.getIsSys() != 1) {
			activePaySql.append("   AND b.user_id  IN (").append(req.getUserIds()).append(")\n"); // -- 渠道权限
		}
		activePaySql.append("       )  a \n");
		activePaySql.append("       GROUP BY  a.chl_app, a.`day` ) c ON c.chl_app = b.chl_app  and  a.active_day = c.day\n");
		activePaySql.append("  )  a \n");
		activePaySql.append("group by ").append("period").append(StringUtils.isNotBlank(req.getQueryColumn()) ? getEndColumnSql(req, "") + " \n" : " \n");
		return activePaySql;
	}


	/**
	 * 活跃设备数
	 * @param req
	 * @param activityRechargeTable
	 * @return
	 *
	 */
	private StringBuilder getActiveDeviceSql(DataReportDto req,String activityRechargeTable,Integer dxAppCode) {
		StringBuilder newDeviceSql = new StringBuilder();
		newDeviceSql.append("select period,\n");
		newDeviceSql.append(StringUtils.isNotBlank(req.getQueryColumn()) ? getStartColumnSql(req, "") : "\t").append("\n")
				.append(" \t0 AS accountNums, -- 新增设备数\n" +
						" \t0 AS accountRegNums,  -- 新增设备注册数\n" +
						" \t0 AS newPayNums,-- 新增付费数\n" +
						" \t0 AS newPayAmounts,-- 新增充值金额\n" +
						" \t0 AS newPayFeeAmounts,-- 新增充值实付金额\n" +
						" \t0 AS periodTotalPayFeeAmount,-- 期内充值实付金额\n" +
						" \tCOUNT(DISTINCT uuid ) AS activeaccounts,  -- 活跃用户数\n" +
						" \t0 AS activeFeeAccounts, -- 活跃付费数\n" +
						" \t0 AS activePayAmounts, -- 活跃充值金额\n" +
						" \t0 AS activePayFeeAmounts,-- 活跃充值实付金额\n" +
						" \t0 AS totalpaynums,-- 累计付费数\n" +
						" \t0 AS totalpayamounts,-- 累计充值金额\n" +
						" \t0 AS totalpayfeeamounts-- 累计充值实付金额\n");
		newDeviceSql.append("from ( \n");
		newDeviceSql.append("SELECT \n");
		newDeviceSql.append(this.getPeriodSql(req)).append(" AS period,");
		newDeviceSql.append("app_code,chl_main,chl_sub,chl_app,uuid").append("\n")
				.append("FROM").append("\n")
				.append(activityRechargeTable).append("\t").append("\n")
				.append("WHERE 1=1 ").append("\n")
				.append("and  dx_app_code = ").append(dxAppCode)
				.append(" and day >= ").append(req.getStartTime()).append(" and day <= ").append(req.getEndTime()).append("\n")
				.append(getWhereCondition(req)).append("\n")
				.append(") a").append("\n");
		newDeviceSql.append("    inner JOIN ( \n");
		newDeviceSql.append("       SELECT \n");
		newDeviceSql.append("          user_id userId, parent_chl, chl, app_chl, op_tag \n");
		newDeviceSql.append("       FROM \n");
		newDeviceSql.append("          ").append(yunYingProperties.getCpsUserPackTable()).append(" \n");
		newDeviceSql.append("        ) c ON c.parent_chl = a.chl_main AND c.chl = a.chl_sub and c.app_chl = chl_app\n");
		newDeviceSql.append("     WHERE\n");
		newDeviceSql.append("        1 = 1 and c.op_tag = 1\n");
		if (req.getIsSys() != 1) {
			newDeviceSql.append("     AND userId IN (").append(req.getUserIds()).append(") \n"); // -- 渠道权限
		}

		newDeviceSql.append("group by ").append("period").append(StringUtils.isNotBlank(req.getQueryColumn()) ? getEndColumnSql(req, "") + " \n" : " \n");
		return newDeviceSql;
	}


	/**
	 * 新增设备数
	 * @param req
	 * @param deviceRegTable
	 * @return
	 *
	 */
	private StringBuilder getNewDeviceSql(DataReportDto req, String deviceRegTable,Integer dxAppCode) {
		StringBuilder newDeviceSql = new StringBuilder();
		newDeviceSql.append("select period,\n");
		newDeviceSql.append(StringUtils.isNotBlank(req.getQueryColumn()) ? getStartColumnSql(req, "") : "\t").append("\n")
				.append(" \tCOUNT(DISTINCT uuid ) AS accountNums, -- 新增设备数\n" +
						" \t0 AS accountRegNums,  -- 新增设备注册数\n" +
						" \t0 AS newPayNums,-- 新增付费数\n" +
						" \t0 AS newPayAmounts,-- 新增充值金额\n" +
						" \t0 AS newPayFeeAmounts,-- 新增充值实付金额\n" +
						" \t0 AS periodTotalPayFeeAmount,-- 期内充值实付金额\n" +
						" \t0 AS activeaccounts,  -- 活跃用户数\n" +
						" \t0 AS activeFeeAccounts, -- 活跃付费数\n" +
						" \t0 AS activePayAmounts, -- 活跃充值金额\n" +
						" \t0 AS activePayFeeAmounts,-- 活跃充值实付金额\n" +
						" \t0 AS totalpaynums,-- 累计付费数\n" +
						" \t0 AS totalpayamounts,-- 累计充值金额\n" +
						" \t0 AS totalpayfeeamounts -- 累计充值实付金额\n");
		newDeviceSql.append("from ( \n");
		newDeviceSql.append("SELECT \n");
		newDeviceSql.append(this.getPeriodSql(req)).append(" AS period,");
		newDeviceSql.append("app_code,chl_main,chl_sub,chl_app,uuid").append("\n")
				.append("FROM").append("\n")
				.append(deviceRegTable).append("\t").append("\n")
				.append("WHERE 1=1 ").append("\n")
				.append("and  dx_app_code = ").append(dxAppCode)
				.append(" and day >= ").append(req.getStartTime()).append(" and day <= ").append(req.getEndTime()).append("\n")
				.append(getWhereCondition(req)).append("\n")
				.append(") a").append("\n");
		newDeviceSql.append("    inner JOIN ( \n");
		newDeviceSql.append("       SELECT \n");
		newDeviceSql.append("          user_id userId, parent_chl, chl, app_chl, op_tag \n");
		newDeviceSql.append("       FROM \n");
		newDeviceSql.append("          ").append(yunYingProperties.getCpsUserPackTable()).append(" \n");
		newDeviceSql.append("        ) c ON c.parent_chl = a.chl_main AND c.chl = a.chl_sub and c.app_chl = chl_app\n");
		newDeviceSql.append("     WHERE\n");
		newDeviceSql.append("        1 = 1 and c.op_tag = 1\n");
		if (req.getIsSys() != 1) {
			newDeviceSql.append("     AND userId IN (").append(req.getUserIds()).append(") \n"); // -- 渠道权限
		}
		newDeviceSql.append("group by ").append("period").append(StringUtils.isNotBlank(req.getQueryColumn()) ? getEndColumnSql(req, "") + " \n" : " \n");
		return newDeviceSql;
	}

	/**
	 * 新增设备注册数
	 * @param req
	 * @param deviceRegTable
	 * @param accountRegTable
	 * @return
	 */
	private StringBuilder getNewDeviceRegSql(DataReportDto req, String deviceRegTable, String accountRegTable,Integer dxAppCode) {
		StringBuilder newDeviceRegSql= new StringBuilder();
		newDeviceRegSql.append("SELECT \n");
		newDeviceRegSql.append( "b.period, \n");
		newDeviceRegSql.append(StringUtils.isNotBlank(req.getQueryColumn()) ? getStartColumnSql(req, "b.") : "\t").append("\n")
				.append("\t0 AS accountNums, -- 新增设备数\n" +
						"\tCOUNT(DISTINCT a.uuid ) AS accountRegNums,  -- 新增设备注册数\n" +
						"\t0 AS newPayNums,-- 新增付费数\n" +
						"\t0 AS newPayAmounts,-- 新增充值金额\n" +
						"\t0 AS newPayFeeAmounts,-- 新增充值实付金额\n" +
						"\t0 AS periodTotalPayFeeAmount,-- 期内充值实付金额\n" +
						"\t0 AS activeaccounts,  -- 活跃用户数\n" +
						"\t0 AS activeFeeAccounts, -- 活跃付费数\n" +
						"\t0 AS activePayAmounts, -- 活跃充值金额\n" +
						"\t0 AS activePayFeeAmounts,-- 活跃充值实付金额\n" +
						"\t0 AS totalpaynums,-- 累计付费数\n" +
						"\t0 AS totalpayamounts,-- 累计充值金额\n" +
						"\t0 AS totalpayfeeamounts -- 累计充值实付金额\n")
				.append("FROM\t").append("   ").append("\t(").append("\n")
				.append("select uuid,min_by(app_code,reg_time) app_code,min_by(app_main,reg_time) app_main,min_by(chl_main,reg_time) chl_main,min_by(chl_sub,reg_time) chl_sub   FROM \t").append("\n")
				.append(accountRegTable).append("\n")
				.append("where 1=1 ").append("\n")
				.append(" and  dx_app_code = ").append(dxAppCode)
				.append(getWhereConditionGameId(req)).append("\n")
				.append("  GROUP BY  uuid ").append("\n")
				.append(") a ").append("\n")
				.append("inner join ( ").append("\n")
				.append("SELECT \n")
				.append(this.getPeriodSql(req)).append(" AS  period,").append(" uuid,app_code,app_main,chl_sub,chl_app  FROM\n")
				.append(deviceRegTable).append("\n")
				.append("WHERE 1=1 ").append("\n")
				.append(" and  dx_app_code =  ").append(dxAppCode)
				.append(" and day >= ").append(req.getStartTime()).append(" and day <= ").append(req.getEndTime()).append("\n")
				.append(getWhereCondition(req)).append("\n")
				.append(") b ").append("\n")
				.append("on a.uuid=b.uuid  and a.app_code=b.app_code ").append("\n");
		newDeviceRegSql.append("    inner JOIN ( \n");
		newDeviceRegSql.append("       SELECT \n");
		newDeviceRegSql.append("          user_id userId, parent_chl, chl, app_chl, op_tag \n");
		newDeviceRegSql.append("       FROM \n");
		newDeviceRegSql.append("          ").append(yunYingProperties.getCpsUserPackTable()).append(" \n");
		newDeviceRegSql.append("        ) c ON  c.chl = b.chl_sub and c.app_chl = b.chl_app\n");
		newDeviceRegSql.append("     WHERE\n");
		newDeviceRegSql.append("        1 = 1 and c.op_tag = 1\n");
		if (req.getIsSys() != 1) {
			newDeviceRegSql.append("     AND userId IN (").append(req.getUserIds()).append(") \n"); // -- 渠道权限
		}
		newDeviceRegSql.append("group by ").append("b.period").append(StringUtils.isNotBlank(req.getQueryColumn()) ? getEndColumnSql(req, "b.") + " \n" : " \n");
		return newDeviceRegSql;
	}

	//where 条件
	//筛选条件
	public String getWhereCondition(DataReportDto req) {
		StringBuilder sqlCondition = new StringBuilder();
		//子游戏
		String gameidArr = req.getGameIdArr();
		//子渠道
		String chlArr = req.getChlArr();
		//分包渠道
		String appchlArr = req.getAppchlArr();
		if (org.apache.commons.lang.StringUtils.isNotBlank(gameidArr)) {
			if (gameidArr.contains(",")) {
				sqlCondition.append(" and app_code in (" + gameidArr + ")");
			} else {
				sqlCondition.append(" and app_code  = " + gameidArr + "");
			}
		}
		if (org.apache.commons.lang.StringUtils.isNotBlank(chlArr)) {
			if (chlArr.contains(",")) {
				sqlCondition.append(" and chl_sub in ('" + chlArr.replaceAll(",", "','") + "')");
			} else {
				sqlCondition.append(" and chl_sub    = '" + chlArr + "'");
			}
		}
		if (org.apache.commons.lang.StringUtils.isNotBlank(appchlArr)) {
			if (appchlArr.contains(",")) {
				sqlCondition.append(" and chl_app in ('" + appchlArr.replaceAll(",", "','") + "')");
			} else {
				sqlCondition.append(" and chl_app  = '" + appchlArr + "'");
			}
		}
		return sqlCondition.toString();
	}


	public String getWhereConditionGameId(DataReportDto req) {
		StringBuilder sqlCondition = new StringBuilder();
		//子游戏
		String gameidArr = req.getGameIdArr();
		if (org.apache.commons.lang.StringUtils.isNotBlank(gameidArr)) {
			if (gameidArr.contains(",")) {
				sqlCondition.append(" and app_code in (" + gameidArr + ")");
			} else {
				sqlCondition.append(" and app_code  = " + gameidArr + "");
			}
		}
		return sqlCondition.toString();
	}


	private StringBuilder getPeriodSql(DataReportDto req) {
		String period = req.getPeriod();
		StringBuilder sql = new StringBuilder();
		sql.append(period);
		return sql;
	}

	/**
	 * 分组查询条件
	 * @param req
	 * @param prefix
	 * @return
	 */
	private StringBuilder getStartColumnSql(DataReportDto req,  String prefix) {
		StringBuilder sql = new StringBuilder();
		//TODO 获取需要那些分区
		String queryColumn = req.getQueryColumn();
		getColumnSql(sql,queryColumn,prefix);
		sql.append(",");
		return sql;
	}

	/**
	 * 分组查询条件
	 * @param req
	 * @param prefix
	 * @return
	 */
	private StringBuilder getEndColumnSql(DataReportDto req,  String prefix) {
		StringBuilder sql = new StringBuilder();
		sql.append(",");
		//TODO 获取需要那些分区
		String queryColumn = req.getQueryColumn();
		getColumnSql(sql,queryColumn,prefix);
		return sql;
	}

	private void getColumnSql(StringBuilder sql,String queryColumn,String prefix) {
		if (StringUtils.isNotEmpty(queryColumn)){
			String[] split = queryColumn.split(",");
			List<String> list = new ArrayList<>();
			for (String s : split) {
				list.add(s);
			}
			if (CollectionUtils.isNotEmpty(list)){
				String collect = list.stream().map(s -> prefix + s).collect(Collectors.joining(", "));
				sql.append(collect);
			}
		}
	}
}


